import requests
import pymysql

api_url = 'https://api.github.com/search/repositories?q=spider'
req = requests.get(api_url)
print('状态码：', req.status_code)
req_dic = req.json()
print('与spider有关的库总数：', req_dic['total_count'])
print('本次请求是否完整：', req_dic['incomplete_results'])
req_dic_items = req_dic['items']
print('当前页面返回的项目数量：', len(req_dic_items))

# 连接数据库
db = pymysql.connect(host='localhost', user='root', password='ywh.2004430', port=3306)
cursor = db.cursor()

# 创建数据库（如果不存在）
cursor.execute("CREATE DATABASE IF NOT EXISTS WEBAPI3 DEFAULT CHARACTER SET utf8mb4")
db.close()

# 连接新创建的数据库
db = pymysql.connect(host='localhost', user='root', password='ywh.2004430', database='WEBAPI3', port=3306)
cursor = db.cursor()

# 删除表（如果存在）
cursor.execute("DROP TABLE IF EXISTS webapi3")

# 创建表
sql1 = """CREATE TABLE IF NOT EXISTS `webapi3` (
    `id` int(10) NOT NULL AUTO_INCREMENT,
    `full_name` char(20) NOT NULL,
    `score` int(10) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;"""
cursor.execute(sql1)
print("Created table Successful.")

# 插入数据
for index, key in enumerate(req_dic_items):
    print('项目序号：', index, '项目名称：', key['full_name'], '项目评分：', key['score'])
    sql2 = 'INSERT INTO webapi3(full_name, score) VALUES(%s, %s)'
    try:
        cursor.execute(sql2, (key['full_name'], key['score']))
        db.commit()
    except Exception as e:
        print(f"Error inserting data: {e}")
        db.rollback()

# 关闭数据库连接
db.close()
